USE OldStuffShopping
GO

------------------------------------------------
-- Trigger : Insert into user account
-- Description: 
------------------------------------------------
IF EXISTS (SELECT name 
			FROM sys.triggers
			WHERE name = 'tggInsteadOfInsertUserAccount')
BEGIN
	DROP TRIGGER [tggInsteadOfInsertUserAccount]
END

GO
CREATE TRIGGER [tggInsteadOfInsertUserAccount] ON [UserAccount]
INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO [UserAccount] 
	(Id, FirstName, LastName, Email, [Password], [CreatedDateTime], [ModifiedDateTime], [Properties])
	SELECT 
	NEWID(), I.FirstName, I.LastName, I.Email, CONVERT(NVARCHAR(40), HASHBYTES('SHA1', I.Password), 2), GETDATE(), GETDATE(), ISNULL(I.[Properties], '{}') FROM inserted AS I
END
GO

------------------------------------------------
-- Trigger : Update user account
-- Description: 
------------------------------------------------
IF EXISTS (SELECT name 
			FROM sys.triggers
			WHERE name = 'tggForUpdateUserAccount')
BEGIN
	DROP TRIGGER [tggForUpdateUserAccount]
END

GO
CREATE TRIGGER [tggForUpdateUserAccount] ON UserAccount
FOR UPDATE
AS BEGIN
	UPDATE UserAccount
	SET 
		 [ModifiedDateTime] = GETDATE()
	FROM inserted AS I
    JOIN UserAccount AS A
        ON (A.Id = I.Id)
            
	IF (UPDATE([CreatedDateTime])) BEGIN
		ROLLBACK
	END

	IF (UPDATE(Password)) BEGIN
		UPDATE UserAccount
		SET 
			 Password = CONVERT(NVARCHAR(40), HASHBYTES('SHA1', I.Password), 2)
		FROM inserted AS I
        JOIN UserAccount AS A
            ON (A.Id = I.Id)
	END
END
GO

------------------------------------------------
-- Trigger : Insert into user stuff
-- Description: 
------------------------------------------------
IF EXISTS (SELECT name 
			FROM sys.triggers
			WHERE name = 'tggInsteadOfInsertUserStuff')
BEGIN
	DROP TRIGGER [tggInsteadOfInsertUserStuff]
END

GO
CREATE TRIGGER [tggInsteadOfInsertUserStuff] ON [UserStuff]
INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO [UserStuff] 
	([UserId], [CategoryId], [Name], [Quantity], [Description], [CreatedDateTime], [ModifiedDateTime], [Properties], [UnitPrice])
	SELECT 
	I.UserId, I.CategoryId, Name, ISNULL(I.Quantity, 0), I.[Description], GETDATE(), GETDATE(), ISNULL(I.[Properties], '{}'), I.[UnitPrice] FROM inserted AS I
	
	SELECT * FROM inserted;
END
GO

------------------------------------------------
-- Trigger : Update user stuff
-- Description: 
------------------------------------------------
IF EXISTS (SELECT name 
			FROM sys.triggers
			WHERE name = 'tggForUpdateUserStuff')
BEGIN
	DROP TRIGGER [tggForUpdateUserStuff]
END

GO
CREATE TRIGGER [tggForUpdateUserStuff] ON UserStuff
FOR UPDATE
AS BEGIN
	UPDATE UserStuff
	SET [ModifiedDateTime] = GETDATE()
	FROM inserted AS I
    JOIN UserStuff AS A
        ON (A.Id = I.Id)
            
	IF (UPDATE([CreatedDateTime])) BEGIN
		ROLLBACK
	END
END

------------------------------------------------
-- Trigger : Insert into category
-- Description: 
------------------------------------------------
IF EXISTS (SELECT name 
			FROM sys.triggers
			WHERE name = 'tggInsteadOfInsertCategory')
BEGIN
	DROP TRIGGER [tggInsteadOfInsertCategory]
END

GO
CREATE TRIGGER [tggInsteadOfInsertCategory] ON [Category]
INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO [Category] 
	([ParentId], [Name], [Description], [CreatedDateTime], [ModifiedDateTime], [Properties])
	SELECT 
	I.ParentId, I.Name, I.[Description], GETDATE(), GETDATE(), ISNULL(I.[Properties], '{}') FROM inserted AS I
END
GO

------------------------------------------------
-- Trigger : Update category
-- Description: 
------------------------------------------------
IF EXISTS (SELECT name 
			FROM sys.triggers
			WHERE name = 'tggForUpdateCategory')
BEGIN
	DROP TRIGGER [tggForUpdateCategory]
END

GO
CREATE TRIGGER [tggForUpdateCategory] ON [Category]
FOR UPDATE
AS BEGIN
	UPDATE Category
	SET [ModifiedDateTime] = GETDATE()
	FROM inserted AS I
    JOIN Category AS A
        ON (A.Id = I.Id)
            
	IF (UPDATE([CreatedDateTime])) BEGIN
		ROLLBACK
	END
END

------------------------------------------------
-- Trigger : Insert into order
-- Description: 
------------------------------------------------
IF EXISTS (SELECT name 
			FROM sys.triggers
			WHERE name = 'tggInsteadOfInsertOrder')
BEGIN
	DROP TRIGGER [tggInsteadOfInsertOrder]
END

GO
CREATE TRIGGER [tggInsteadOfInsertOrder] ON [Order]
INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO [Order]
	([StuffId], [UserOrderId], [Status], [OfferMoney], [PaymentType], [CreatedDateTime], [ModifiedDateTime], [Properties])
	SELECT 
	I.[StuffId], I.[UserOrderId], I.[Status], I.[OfferMoney], I.[PaymentType], GETDATE(), GETDATE(), ISNULL(I.[Properties], '{}') FROM inserted AS I
END
GO

------------------------------------------------
-- Trigger : Update order
-- Description: 
------------------------------------------------
IF EXISTS (SELECT name 
			FROM sys.triggers
			WHERE name = 'tggForUpdateOrder')
BEGIN
	DROP TRIGGER [tggForUpdateOrder]
END

GO
CREATE TRIGGER [tggForUpdateOrder] ON [Order]
FOR UPDATE
AS BEGIN
	UPDATE [Order]
	SET [ModifiedDateTime] = GETDATE()
	FROM inserted AS I
    JOIN [Order] AS A
        ON (A.Id = I.Id)
            
	IF (UPDATE([CreatedDateTime])) BEGIN
		ROLLBACK
	END
END